/*
THIS PROC IS TO RETRIVE ALL THE CLOSED LOTS INFO FOR SORT PROCESS FROM SORT LOT HISTORY(LOT_ID) AND INFORMATIVE BINNING(QTY'S) TABLE.
exec sp_sort_history_report 'sort','0','0','0','0','0','XLREPORT', '0'
*/
ALTER proc sp_sort_history_report (
 
@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS	VARCHAR(100),
@BASEPRODUCT_ID		VARCHAR(50),
@VENDOR			    VARCHAR(50),
@FROMDATE		    VARCHAR(50),
@TODATE 		    VARCHAR(50),
@REPORT_TYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50) 
)
AS
BEGIN
 	declare @qty_in int, @qty_out int, @yield float,@die_wafer int
 	declare @date_in datetime, @date_out datetime
 	declare @temp_lot_id int
	declare @wafer_qty int
 	declare @lot_number varchar(50)
	declare @vendor_name varchar(50)
 	declare @temp_process_type varchar(15)	
 	declare @bin1 int, @bin2 int, @bin3 int, @bin4 int, @bin5 int, @bin6 int, @bin7 int, @bin8 int, @bin9 int, @bin10 int
 	declare @bin11 int,@bin12 int, @bin13 int,@bin14 int,@bin15 int, @sort_process_step_id int
 	declare @wafer_count int, @marketing_part_number varchar(50)
 	declare @goodbins int,@badbins int ,@bin_rejected int
 	declare @gooddice int
 	declare @prostepid int
 	declare @all_process_steps int
    declare @qtyout int
	declare @good_dice int

	declare @vendor_lot varchar(1000)
 	
 	
 	-- Temporarty table to display the result of Bins information with lotnumber and partnumber for the wafers
 	declare @results Table(MarketingPartNumber varchar(25), Lot_Number varchar(50),Wafer_Qty int,Total int,
 				GoodBins int,BadBins int,Bin_1 int,Bin_2 int,Bin_3 int,Bin_4 int,Bin_5 int,Bin_6 int,Bin_7 int,Bin_8 int,
 					Bin_9 int,Bin_10 int,Bin_11 int,Bin_12 int,Bin_13 int,Bin_14 int,Bin_15 int,Date_in 
 						datetime, Date_out datetime,Yield float,GrossDie int, Vendor_Lot varchar(1000),Bin_Rejected int, ps_type varchar(50), vendor_name varchar(50) )
 
 
 	declare @SQLString NVARCHAR(4000)
 	declare @ParamDefinition NVARCHAR(500)
 
 	set @SQLString = N' declare lotCursor CURSOR for select P.ProcessStep_ID, Part_Number, ps_type, L_Vendor_Name,LT.Lot_ID,Lot_Number,SUM(Wafer_Qty_IN),MIN(isnull(Date_In,LT.timestamp)),MAX(isnull(Date_Out,SL.timestamp))
						,SUM(Good_Die_QTY),AVG(SL_Yield) from product P, Process_Step PS, Lot_table LT, Location L, Sort_Lot SL
						where PS.ProcessStep_ID = P.ProcessStep_ID and LT.processStep_ID = PS.ProcessStep_ID AND LT.LOCATION_ID = L.LOCATION_ID 
						and LT.Lot_ID = SL.lot_id  and Lot_Current_Status<>''Running'' AND ( p_type is null or p_type = ''BOARD'' or p_type = ''KIT'' )'
 
 	--set @ParamDefinition = N'@Prm_ProcessStep_ID VARCHAR(50), @Prm_Process_Type varchar(50)'
 	
	IF ( @PROCESS_TYPE IS NOT NULL  AND @PROCESS_TYPE <> '0' )
	BEGIN
			SET @SQLString = @SQLSTRING + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END

	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.DATE_OUT >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.DATE_OUT <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END
 		 
 	 	
 	set @SQLString = @SQLString + ' group by  P.ProcessStep_ID,Part_Number,ps_type, L_Vendor_Name, LT.Lot_ID,Lot_Number order by Part_Number,Min(Date_In),Max(Date_Out) '
 
 	print @SQLString
 	execute sp_executesql @SQLString --, @ParamDefinition, @Prm_ProcessStep_ID = @PROCESSSTEP_IDS, @Prm_Process_Type = @PROCESS_TYPE
 
 	open lotCursor 
 	fetch next from lotCursor into @sort_process_step_id, @marketing_part_number, @temp_process_type,@vendor_name,
 			@temp_lot_id, @lot_number, @wafer_qty, @date_in, @date_out, @qtyout, @yield
 
 	while @@fetch_status = 0
 	begin

		-- To get the Gross die per wafer			
		select @die_wafer = parameter_value from Process_Parameter where Parameter_key = 'Gross Die/Wafer' and ProcessStep_id  = ( Select ProcessStep_id from  fn_get_process_flow_by_processstep_id(@sort_process_step_id) where Process_Type_Name = 'Fab' )
	
		declare @validBinsCount int
		set @validBinsCount = 0;
		declare @temp_gross_die int 
 
 		declare waferCursor CURSOR For
 		select sum(Bin_1),sum(Bin_2),sum(Bin_3),sum(Bin_4),sum(Bin_5),sum(Bin_6),sum(Bin_7),sum(Bin_8),sum(Bin_9),sum(Bin_10),sum(Bin_11),sum(Bin_12),sum(Bin_13),sum(Bin_14),
 				         sum(Bin_15),sum(Bin_rejected), sum(Good_Dice), count(convert(int,Wafer_id))  from Informative_Binning where Lot_ID = @temp_lot_id
 		open waferCursor  
 		
 		fetch next from waferCursor into @bin1,@bin2,@bin3,@bin4,@bin5,@bin6,@bin7,@bin8,@bin9,
 								      @bin10,@bin11,@bin12,@bin13,@bin14,@bin15,@bin_rejected, @good_dice, @wafer_count

		if ( @die_wafer > 0 and @die_wafer is not null)
		begin
		set @temp_gross_die = (@wafer_count * @die_wafer)
		end
		if @yield is NULL OR @yield = 0.0
		set @yield = (convert(float,@good_dice) * 100 )/convert(float,@temp_gross_die ) 		

 		while @@fetch_status = 0
 		begin
 
 			if( @bin1 is not null )				 
 			begin
 				set @validBinsCount = @validBinsCount + 1
 			end 
 				
 			if( @bin2 is not null )				 
 			begin
 			   	set @validBinsCount = @validBinsCount + 1
 			end 	
 			if( @bin3 is not null )				 
 			begin
 			   	set @validBinsCount = @validBinsCount + 1
 			end 	
 			if( @bin4 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end 	
 			if( @bin5 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end 	
 			if( @bin6 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end
 			if( @bin7 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end 	
 			if( @bin8 is not null )				 
 			begin
 			   	set @validBinsCount = @validBinsCount + 1
 			end 	
 			if( @bin9 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end
 			if( @bin10 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end
 			if( @bin11 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end 
 			if( @bin12 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end
 			if( @bin13 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end 
 			if( @bin14 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end	
 			if( @bin15 is not null )				 
 			begin
 			    	set @validBinsCount = @validBinsCount + 1
 			end
 			
			if @validBinsCount = 0
			    set @goodbins = 1
			set @goodbins = @validBinsCount
			
 			if @good_dice > 0
			begin

	 			insert into @results (MarketingPartNumber,Lot_Number,Wafer_Qty,Total,GoodBins,BadBins,Bin_1,Bin_2,Bin_3,Bin_4,Bin_5,
	 						       Bin_6,Bin_7,Bin_8,Bin_9,Bin_10, Bin_11,Bin_12,Bin_13,Bin_14,Bin_15,
	 						       Date_in,Date_out,Yield,GrossDie,Vendor_Lot, ps_type, vendor_name) 
	 			values(@marketing_part_number,@lot_number,@wafer_count,@good_dice,@goodbins,@badbins,@bin1,@bin2,@bin3,@bin4,@bin5,@bin6,
	 				            @bin7,@bin8,@bin9,@bin10,@bin11,@bin12,@bin13,@bin14,@bin15,@date_in,
	 					@date_out,ROUND(@yield,2),@die_wafer,@vendor_lot,@temp_process_type, @vendor_name )
	
 			end	
 				
 			fetch next from waferCursor into @bin1,@bin2,@bin3,@bin4,@bin5,@bin6,@bin7,@bin8,
 								     @bin9,@bin10,@bin11,@bin12,@bin13,@bin14,@bin15,@bin_rejected, @good_dice, @wafer_count		
 		end
 		close waferCursor
 		deallocate waferCursor
 
 	fetch next from lotCursor into @sort_process_step_id, @marketing_part_number, @temp_process_type,@vendor_name,
 			@temp_lot_id, @lot_number, @wafer_qty, @date_in, @date_out, @qtyout, @yield
 	end
 	close lotCursor
 	deallocate lotCursor

 	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT
		MARKETINGPARTNUMBER AS [PART NUMBER] , 
		LOT_NUMBER AS [LOT NUMBER],
		VENDOR_NAME AS [VENDOR],
		PS_TYPE AS [ PROCESS],
		DATE_IN AS [DATE IN],
		DATE_OUT AS [DATE OUT],
		WAFER_QTY AS [WAFERS],
		GROSSDIE AS [GROSS DIE],
		TOTAL AS [DIE OUT],
		YIELD  ,
		BIN_1 AS[BIN 1], 
		BIN_2 AS[BIN 2],
		BIN_3 AS[BIN 3],
		BIN_4 AS[BIN 4],
		BIN_5 AS[BIN 5 ] 
		FROM @RESULTS ORDER BY MARKETINGPARTNUMBER,LOT_NUMBER, VENDOR_NAME
	END
	ELSE
	BEGIN
		SELECT * FROM @RESULTS ORDER BY MARKETINGPARTNUMBER,LOT_NUMBER,VENDOR_NAME
	END
END
 